##use strict;
use HTML::TreeBuilder;
use File::Path;
use Time::Local 'timelocal';
#use warnings;
use File::Spec::Functions;
use Cwd;
use Win32::OLE;
use MIME::Lite;
## Static variable Define##
my $MP_Filename ="China_Product_SW_Update_Schedule_MP.xlsx";
my $SD_Filename ="China_Product_SW_Update_Schedule_SD.xlsx";
my $currentdir= "C:\\FileChangeNotifyTool";
my $oldfilepath= "C:\\FileChangeNotifyTool\\Old_Files";
my $currentTestFilePath = "C:\\";
my $MP_Time_Stamp_path = $oldfilepath.'\\MP_Time_Stamp.txt';
my $SD_Time_Stamp_path = $oldfilepath.'\\SD_Time_Stamp.txt';
my $Old_MP_File_Path = $oldfilepath.'\\'.$MP_Filename;
my $Old_SD_File_Path = $oldfilepath.'\\'.$SD_Filename;
my $Correct_MP_File_Path = $currentTestFilePath.$MP_Filename;
my $Correct_SD_File_Path = $currentTestFilePath.$SD_Filename;
## End Static variable Define##

#************Main Scirpts*************#
my $MPsheet="MP Products";
($MPdiflag,@MPresultarray) = &excelCompare($Old_MP_File_Path,$Correct_MP_File_Path,$MPsheet);
print "$MPdiflag\n";
print "\n";

my $SDsheet="SD Products";
($SDdiflag,@SDresultarray) = &excelCompare($Old_SD_File_Path,$Correct_SD_File_Path,$SDsheet);
print "$SDdiflag\n";
print "\n";
print "@temp_x";
print "\n";

print "end\n";
my $SDid = "SD";
&send_email($SDid, @SDresultarray);
#************Main Scirpts End*************#


##Functions##########################################################
sub send_email {
	($fileid,@results)=@_;
	print "Start Send Mail\n";
    #my $self = shift;
    my $to_address = 'ext-duan.ken@nokia.com';
    #my @to_address =('ext-duan.ken@nokia.com','');
    #my $subject = encode_mimewords("Verification Message for HTML mail sending",'Charset','GB2312');
    my $subject = "China SD_MP Product SW Update Notification";
	


my $main_info_temp=<<EOF;
<tr class="Content">
<td id="col_b">var_B</td><td id="col_c">var_B</td><td id="col_d">var_D</td><td id="col_e">var_E</td><td id="col_f">var_F</td><td id="col_g">var_G</td><td id="col_h">var_H</td>
<td id="col_1st">var_I</td><td id="col_1st">var_J</td><td id="col_1st">var_K</td>
<td id="col_2nd">var_L</td><td id="col_2nd">var_M</td><td id="col_2nd">var_N</td>
<td id="col_3rd">var_O</td><td id="col_3rd">var_P</td><td id="col_3rd">var_Q</td>
<td id="col_4th">var_R</td><td id="col_4th">var_S</td><td id="col_4th">var_T</td>
<td id="col_comment">var_U</td>
</tr>
EOF
    my $data =<<EOF;
<html>
<style type="text/css">
body {font-family: Calibri;color:black;}
table {background-color:#D2D2D2; text-align:center;}
body {background-color:white}
#col_b {}
#col_c {display:none;}
#col_d {display:none;}
#col_e {display:none;}
#col_f {display:none;}
#col_g {}
#col_h {}
#col_1st {display:none;}
#col_2nd {display:none;}
#col_3rd {display:none;}
#col_4th {display:none;}
#col_comment {}

</style>
<body>
<h3>Dear fellas:</h3>
<p>The  Product_excel_sheet is updated as below:</p>
<br />

<table align="left" border="0" width="100%" cellpadding="0" cellspacing="0">
<tr class="row1">
<th rowspan="2"id="col_b" bgcolor=#7FD4FF>Name</th><th rowspan="2" id="col_c" bgcolor=#DAEEF3>Model No</th><th rowspan="2"id="col_d" bgcolor=#DAEEF3>Platform</th><th rowspan="2"id="col_e" bgcolor=#DAEEF3>MPM</th><th rowspan="2"id="col_f" bgcolor=#DAEEF3>Program PD3</th><th rowspan="2" id="col_g" bgcolor=#DAEEF3>Device GTM4(Sales Start)</th><th rowspan="2" id="col_g" bgcolor=#FDE9D9>Current Production SW </th>
<td colspan="3"id="col_1st" bgcolor=#CCC0DA><b>1st Update</b></td><td colspan="3" id="col_2nd" bgcolor=#CCC0DA><b>2nd Update</b></td><td colspan="3"id="col_3rd" bgcolor=#CCC0DA><b>3rd Update</b></td><td colspan="3"id="col_4th"bgcolor=#CCC0DA><b>4th Update</b></td><th rowspan="2"id="col_comment"bgcolor=#D2FDD2>Comments</th>
</tr>

<tr class="row2" bgcolor=#CCC0DA>
<td id="col_1st">PR release</td><td id="col_1st">|Schedule/SW</td><td id="col_1st">|Approval</td>
<td id="col_2nd">PR release</td><td id="col_2nd">|Schedule/SW</td><td id="col_2nd">|Approval</td>
<td id="col_3rd">PR release</td><td id="col_3rd">|Schedule/SW</td><td id="col_3rd">|Approval</td>
<td id="col_4th">PR release</td><td id="col_4th">|Schedule/SW</td><td id="col_4th">|Approval</td>
</tr>


<!--tr class="Content">
<td id="col_b">var_B</td><td id="col_c">var_B</td><td id="col_d">var_D</td><td id="col_e">var_E</td><td id="col_f">var_F</td><td id="col_g">var_G</td><td id="col_h">var_H</td>
<td id="col_1st">var_I</td><td id="col_1st">var_J</td><td id="col_1st">var_K</td>
<td id="col_2nd">var_L</td><td id="col_2nd">var_M</td><td id="col_2nd">var_N</td>
<td id="col_3rd">var_O</td><td id="col_3rd">var_P</td><td id="col_3rd">var_Q</td>
<td id="col_4th">var_R</td><td id="col_4th">var_S</td><td id="col_4th">var_T</td>
<td id="col_comment">var_U</td>
</tr-->

EOF



my $data_end=<<EOF;
</table>

<br />
<br />
<p>You can click <a href="\\\\begrp101\\GROUPS5\\NMP\\PMKT\\ProductsSWUpdates\\Product_excel_sheet">Here</a> to open it</p>
<br /> 
<p>BRs
<br /> QA Automation</p>
</body>
</html>
EOF
#Change the file name in mail html
if ($fileid eq "SD"){
$data =~ s/Product_excel_sheet/$SD_Filename/;
$subject =~ s/SD_MP/SD/;
$data_end =~ s/Product_excel_sheet/$SD_Filename/;

}

if ($fileid eq "MP"){
$data =~ s/Product_excel_sheet/$MP_Filename/;
$subject =~ s/SD_MP/MP/;
$data_end =~ s/Product_excel_sheet/$MP_Filename/;
}

$Fulldata=$data;
#CSS Display Flags
my $flag1stflag = 0;
my $flag2ndflag = 0;
my $flag3rdflag = 0;
my $flag4thflag = 0;


for my $i (0 .. $#results){
	my $main_info_buffer = $main_info_temp;
    $main_info_buffer =~ s/var_B/$results[$i][0]/;
    $main_info_buffer =~ s/var_C/$results[$i][1]/;
    $main_info_buffer =~ s/var_D/$results[$i][2]/;
    $main_info_buffer =~ s/var_E/$results[$i][3]/;
    $main_info_buffer =~ s/var_F/$results[$i][4]/;
    $main_info_buffer =~ s/var_G/$results[$i][5]/;
    $main_info_buffer =~ s/var_H/$results[$i][6]/;
	#1st group
    $main_info_buffer =~ s/var_I/$results[$i][7]/;
    $main_info_buffer =~ s/var_J/$results[$i][8]/;
    $main_info_buffer =~ s/var_K/$results[$i][9]/;
	#$result = $var =~ /abc/;
	if(($results[$i][7]=~/=>/) or ($results[$i][8]=~/=>/) or ($results[$i][9]=~/=>/)){
		$flag1stflag=1;
	}
	#2nd group
    $main_info_buffer =~ s/var_L/$results[$i][10]/;
    $main_info_buffer =~ s/var_M/$results[$i][11]/;
    $main_info_buffer =~ s/var_N/$results[$i][12]/;
	if(($results[$i][10]=~/=>/) or ($results[$i][11]=~/=>/) or ($results[$i][12]=~/=>/)){
		$flag2ndflag=1;
	}
	#3rd group
    $main_info_buffer =~ s/var_O/$results[$i][13]/;
    $main_info_buffer =~ s/var_P/$results[$i][14]/;
    $main_info_buffer =~ s/var_Q/$results[$i][15]/;
	if(($results[$i][13]=~/=>/) or ($results[$i][14]=~/=>/) or ($results[$i][15]=~/=>/)){
		$flag3rdflag=1;
	}
	#4th group
    $main_info_buffer =~ s/var_R/$results[$i][16]/;
    $main_info_buffer =~ s/var_S/$results[$i][17]/;
    $main_info_buffer =~ s/var_T/$results[$i][18]/;
	if(($results[$i][16]=~/=>/) or ($results[$i][17]=~/=>/) or ($results[$i][18]=~/=>/)){
	$flag4thflag=1;
	}
	#comment
    $main_info_buffer =~ s/var_U/$results[$i][19]/;

	$Fulldata = $Fulldata.$main_info_buffer;
}
# CSS selection Cases
if ($flag1stflag ==1){
	$Fulldata =~ s/#col_1st {display:none;}/#col_1st {}/;
}
if ($flag2ndflag ==1){
	$Fulldata =~ s/#col_2nd {display:none;}/#col_2nd {}/;
}
if ($flag3rdflag ==1){
	$Fulldata =~ s/#col_3rd {display:none;}/#col_3rd {}/;
}
if ($flag4thflag ==1){
	$Fulldata =~ s/#col_4th {display:none;}/#col_4th {}/;
}

#Add the end HTML string
$Fulldata=$Fulldata.$data_end;

print $Fulldata;
    my $msg = MIME::Lite->new (
        From => 'mape2emonitoring_SVC_BE@nokia.com',
        To => $to_address,
        Subject => $subject,
        Type     => 'text/html',
        Data     => $Fulldata,
        Encoding => 'base64',
    ) or die "create container failed: $!";

    $msg->attr('content-type.charset' => 'ANSI');
    $msg->send('smtp','smtp.nokia.com',Debug=>0);
	print "Mail sending is finished.\n";
}


sub excelCompare{
	my($oldfilepath,$newfilepath,$sheetname)=@_;
	my @returnarray;
	my $diff;
	print "$oldfilepath\n";
	print "$newfilepath\n";
	
	print "MP Excel Compare Tool Start..\n\n";
	my $Excel_New = Win32::OLE->new("Excel.Application","Quit");
	#$ExcelNew->{DisplayAlerts}=0;
	my $MP_New = $Excel_New->Workbooks->Open( $newfilepath );
	my $MP_sheet_New = $MP_New->Worksheets($sheetname); 


	my $Excel_Old = Win32::OLE->new("Excel.Application","Quit");
	my $MP_Old = $Excel_Old->Workbooks->Open( $oldfilepath);
	my $MP_sheet_Old= $MP_Old->Worksheets($sheetname); 

	
	
	#my @letter_per=("F".."T");
for my $row (6..61){
		my $diffflag =0;
		my @temprow;
		for my $col ("F".."T"){
				my $oldvalue = $MP_sheet_Old->Range($col.$row)->{Value};
				my $newvalue = $MP_sheet_New->Range($col.$row)->{Value};
				if($oldvalue eq $newvalue){
				#print "_";
				#print $oldvalue;
				} else {
				#print "X";
				$diffflag =1;
				$diff =1;
				$newvalue ="=>".$newvalue;
				#print $oldvalue;
				}
				push(@temprow, $newvalue);
				}

		#print "Diff flag is: $diffflag";		
		#print "\n";
		if($diffflag ==1){
		#print "@temprow \n";
		push(@returnarray, [@temprow]); 
		}
}

$Excel_New->Quit;
$Excel_Old->Quit;
$MP_New =0;
$MP_Old = 0;
$Old_MP= 0;
$MP_sheet_New =0; 
$MP_sheet_Old = 0;
$Excel_New =0;
$Excel_Old =0;


return($diff, @returnarray);
}



sub sendErrorAlert{
	my $senderror = $currentdir.'\send_email_error.py';
	system($senderror);
	print "Error occurs. Send error message.\n\n";
}

sub sendInitSuccess{
	my $sendInit = $currentdir.'\send_email_init.py';
	system($sendInit);
	print "Init Success.\n\n";
}




sub sendModifyAlert{
	my ($mailSelection)=@_;
	if($mailSelection eq "MP"){
	my $sendalertsuccess = $currentdir.'\send_email2.py';
	system($sendalertsuccess);
	print "MP Modify alert is sent.\n\n";
	}
	if($mailSelection eq "SD"){
	my $sendalertsuccess = $currentdir.'\send_email.py';
	system($sendalertsuccess);
	print "SD Modify alert is sent.\n\n";
	}
}


sub insertTimeStamp{
	my($update_log_path,$timestamp)=@_;
	my $temppath='>'.$update_log_path;
	open(TS_FILE, $temppath) or die "Can't open $temppath.\n";
	print TS_FILE $timestamp;
	print TS_FILE "\n";
	close(TS_FILE);	
}

sub getModifyTime{
	my ($File_Path) = @_;
	my $L_file_modify_time ='';
	my @File_Attributes =stat($File_Path);
	if ($File_Attributes[9] eq ''){
		print "File Not found\n";
		##TODO Call warning mail
		&sendErrorAlert();
		return $L_file_modify_time;
		} else {
		$L_file_modify_time = $File_Attributes[9];
		print "File modify time stamp is $L_file_modify_time\n";
		return $L_file_modify_time;
		}
}

sub getLatestModifyTime{
	my($LogFilePath)=@_;
	open(LOG_FILE, $LogFilePath) or die "Can't open $LogFilePath\n";
	my $Last_Modify_Times ='';
	while(<LOG_FILE>)
		{
			chomp;
			#print "$_\n";
			$Last_Modify_Times = $_;
		}	
	close(LOG_FILE);
	print "Latest Log Modify file Time is $Last_Modify_Times\n";
	return $Last_Modify_Times;
}


##Functions##########################################################
